USE [HuberRace]
GO

/****** Object:  View [dbo].[ResultsWithAG]    Script Date: 07/26/2012 21:13:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[ResultsWithAG_forUSATF]
AS
SELECT * FROM 
(
	SELECT place, time, bib, name, age, gender, AG,
		ROW_NUMBER() OVER (PARTITION BY raceId,gender ORDER BY place ASC) as 'GenderPlace',
		ROW_NUMBER() OVER (PARTITION BY raceId,ag ORDER BY place ASC) as 'AG Place',
		TeamName,
		isUSATF,
		(CASE 
			WHEN isUSATF = 'Y' THEN
				ROW_NUMBER() OVER 
					(PARTITION BY 
						raceId,
						CASE WHEN age < 40 THEN 'O' ELSE 'M' END,
						isUSATF 
					ORDER BY place ASC) 
			ELSE NULL
			END
		) as 'USATF Place',
		
        raceId
	FROM Results
) r


GO


